package com.xwdeshj.dal;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xwdeshj.utils.DateUtil;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;

/**
 * 农家乐店铺 美食信息—SQL类
 * 因jdbcTemplate 加载有相应的顺序，故在该类注解就会报错。
 */
public class Dal_Xwfarmfood {

    /**
     *  获取农家乐店铺 美食信息
     */
    public JSONObject getfarmfoodlist(Integer pageindex, Integer pagesize, String starttime, String endtime,
                                      String sortfild, String sortway, String searchval,String searchkey,
                                      String farm_encryptId,JdbcTemplate jdbcTemplate){
        JSONObject result = new JSONObject();

        String sql = "SELECT\n" +
                "  farm_food_id AS food_id,\n" +
                "  farm_encryptId AS farm_encryptId,\n" +
                "  farm_food_type AS food_type ,\n" +
                "  farm_food_desc AS food_desc,\n" +
                "  farm_food_price AS food_price ,\n" +
                "  farm_food_number AS food_number,\n" +
                "  farm_food_createtime AS createtime\n" +
                "FROM xw_farm_foodinfo ";
        StringBuffer sb = new StringBuffer();
        sb.append(" 1=1 ");

        if(!starttime.equals("")){
            starttime += " 00.00.01";
            endtime += " 23.59.59";
            sb.append(" and farm_food_createtime >= '"+starttime+"' and farm_food_createtime <= '"+ endtime + "' ");
        }
        //店铺
        if(!farm_encryptId.equals("")){
            sb.append(" and farm_encryptId in('" + farm_encryptId + "') ");
        }

        //搜索内容查询
        if (!searchval.equals("")){
            sb.append(" and ( "+searchkey+" like '%" +searchval + "%' )");
        }

        //排序
        if (!sortfild.equals("")) {
            sb.append(" order by " + sortfild + " " + sortway + " ");
        }

        try {

            sql = sql + " where " + sb.toString() + " limit " + (pageindex - 1) * pagesize + "," + pagesize;
            //分页SQL
            String sql2 = "SELECT\n" +
                    "count(*)\n" +
                    "FROM xw_farm_foodinfo" +
                    " WHERE "+ sb.toString();

            SqlRowSet rs = jdbcTemplate.queryForRowSet(sql);
            SqlRowSet rs2 = jdbcTemplate.queryForRowSet(sql2);
            result.put("status", 0);
            result.put("msg", "ok");
            while (rs2.next()) {
                Integer count = rs2.getInt(1);
                result.put("recordcount", count);
                if (count % pagesize == 0) {
                    result.put("pagecount", count / pagesize);
                } else {
                    result.put("pagecount", count / pagesize + 1);
                }
            }

            JSONArray jsonArray = new JSONArray();
            while (rs.next()) {
                JSONObject object = new JSONObject();
                object.put("food_id", (rs.getString("food_id") == null ? "" : rs.getString("food_id")));
                object.put("farm_encryptId", (rs.getString("farm_encryptId") == null ? "" : rs.getString("farm_encryptId")));
                object.put("food_type", (rs.getString("food_type") == null ? "" : rs.getString("food_type")));
                object.put("food_desc", (rs.getString("food_desc") == null ? "" : rs.getString("food_desc")));
                object.put("food_price", (rs.getString("food_price") == null ? "" : rs.getString("food_price")));
                object.put("food_number", (rs.getString("food_number") == null ? "" : rs.getString("food_number")));
                object.put("createtime", (rs.getString("createtime") == null ? "" : DateUtil.DateToString(rs.getDate("createtime"))));
                jsonArray.add(object);
            }
            result.put("list", jsonArray);


        }catch (Exception e){
            e.printStackTrace();
            result.put("status", 40002);
            result.put("msg", e);
        }
        return result;
    }

}
